﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using QuanLyDoanVien.General;
using System.Data;
using QuanLyDoanVien.Bussiness;
namespace QuanLyDoanVien.DataLayer
{
    class HocSinhData
    {
        DataService m_HocSinhData = new DataService();
        List<DataRow> row_HocSinh = new List<DataRow>();
        
        public List<HocSinhInfo> layDSHS()
        {
            DataService.OpenConnection();
            List<HocSinhInfo> rs = new List<HocSinhInfo>();
            string sql = "select * from Tab_HocSinh";
            SqlCommand command = new SqlCommand(sql);
         
            m_HocSinhData.Load(command);
            foreach (DataRow dr in m_HocSinhData.Rows)
            {
                HocSinhInfo hs = new HocSinhInfo();
                try
                {
                    hs.MaHocSinh = dr["MaHocSinh"].ToString();
                    hs.HoTen = dr["HoTen"].ToString();
                    hs.MaLop = dr["MaLop"].ToString();
                    hs.NgaySinh = dr["NgaySinh"].ToString();
                    hs.NgayVaoDoan = dr["NgayVaoDoan"].ToString();
                    hs.HoTenCha = dr["HoTenCha"].ToString();
                    hs.HoTenMe = dr["HoTenMe"].ToString();
                    hs.SoDTCha = dr["SoDTCha"].ToString();
                    hs.SoDTMe = dr["SoDTMe"].ToString();
                    hs.SoDTHS = dr["SoDTHS"].ToString();
                    hs.TuoiCha = int.Parse(dr["TuoiCha"].ToString());
                    hs.TuoiMe = int.Parse(dr["TuoiMe"].ToString());
                    hs.NgheCha = dr["NgheCha"].ToString();
                    hs.NgheMe = dr["NgheMe"].ToString();
                    hs.Email = dr["Email"].ToString();
                    hs.NoiSinh = dr["NoiSinh"].ToString();
                    hs.GioiTinh = dr["GioiTinh"].ToString();
                    hs.THCS = dr["THCS"].ToString();
                    hs.MaChucVu = dr["MaChucVu"].ToString();
                    hs.DiemBiThu = float.Parse(dr["DiemBiThu"].ToString());
                    hs.NhanXet = dr["NhanXet"].ToString();
                    hs.AnhHocSinh = dr["AnhHocSinh"].ToString();
                    hs.RaTruong = int.Parse(dr["RaTruong"].ToString());
                    hs.DoanVien = int.Parse(dr["DoanVien"].ToString());
                }
                catch
                {
                }
                rs.Add(hs);

            }
            m_HocSinhData.CloseConnection();
            return rs;
        }

        public string getAutoID()
        {
            DataService.OpenConnection();
            QuyDinh qd = new QuyDinh();
            string id = "";
            string sql = "select top 1 MaHocSinh from Tab_HocSinh Order By MaHocSinh desc";
            SqlCommand command = new SqlCommand(sql);
            m_HocSinhData.Load(command);
            if (m_HocSinhData.Rows.Count < 1)
                return "HS00000001";
            id = m_HocSinhData.Rows[0][0].ToString();
            int num = int.Parse(id.Substring(2, 8)) + 1;
            id = "HS" + qd.LaySTT(num);
            m_HocSinhData.CloseConnection();
            return id;
        }

        public List<HocSinhInfo> layHocSinhTheoLop(String maLop)
        {
            DataService.OpenConnection();
            List<HocSinhInfo> rs = new List<HocSinhInfo>();
            string sql = "select * from Tab_HocSinh where MaLop=@MaLop And RaTruong=0";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaLop", SqlDbType.Char).Value = maLop;
            m_HocSinhData.Load(command);
            foreach (DataRow dr in m_HocSinhData.Rows)
            {
                HocSinhInfo hs = new HocSinhInfo();
                try
                {
                    hs.MaHocSinh = dr["MaHocSinh"].ToString();
                    hs.HoTen = dr["HoTen"].ToString();
                    hs.MaLop = dr["MaLop"].ToString();
                    hs.NgaySinh = dr["NgaySinh"].ToString();
                    hs.NgayVaoDoan = dr["NgayVaoDoan"].ToString();
                    hs.HoTenCha = dr["HoTenCha"].ToString();
                    hs.HoTenMe = dr["HoTenMe"].ToString();
                    hs.SoDTCha = dr["SoDTCha"].ToString();
                    hs.SoDTMe = dr["SoDTMe"].ToString();
                    hs.SoDTHS = dr["SoDTHS"].ToString();
                    hs.TuoiCha = int.Parse(dr["TuoiCha"].ToString());
                    hs.TuoiMe = int.Parse(dr["TuoiMe"].ToString());
                    hs.NgheCha = dr["NgheCha"].ToString();
                    hs.NgheMe = dr["NgheMe"].ToString();
                    hs.Email = dr["Email"].ToString();
                    hs.NoiSinh = dr["NoiSinh"].ToString();
                    hs.GioiTinh = dr["GioiTinh"].ToString();
                    hs.THCS = dr["THCS"].ToString();
                    hs.MaChucVu = dr["MaChucVu"].ToString();
                    hs.DiemBiThu = float.Parse(dr["DiemBiThu"].ToString());
                    hs.NhanXet = dr["NhanXet"].ToString();
                    hs.AnhHocSinh = dr["AnhHocSinh"].ToString();
                    hs.RaTruong = int.Parse(dr["RaTruong"].ToString());
                    hs.DoanVien = int.Parse(dr["DoanVien"].ToString());
                }
                catch
                {
                }
                rs.Add(hs);

            }
            m_HocSinhData.CloseConnection();
            return rs;
        }

        public DataTable layHocSinhTheoLop1(String maLop)
        {
            DataService.OpenConnection();
            string sql = "select distinct hs.MaHocSinh, hs.HoTen, hs.NgaySinh, hs.NgayVaoDoan, l.TenLop, hs.GioiTinh, cv.TenChucVu, hs.Email, hs.THCS, hs.NhanXet";
            sql += " from (Tab_HocSinh hs inner join Tab_Lop l on hs.MaLop=l.MaLop)";
            sql += " inner join Tab_ChucVu cv on hs.MaChucVu=cv.MaChucVu";
            sql+=" where hs.MaLop='"+maLop+"' And hs.RaTruong=0";
            SqlCommand command = new SqlCommand(sql);
            
            m_HocSinhData.Load(command);

            m_HocSinhData.CloseConnection();
            return m_HocSinhData;
        }
        public String layTenHocSinh(string maHS)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("SELECT HoTen FROM Tab_HocSinh where MaHocSinh=@MaHocSinh");
            cmd.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHS;
            m_HocSinhData.Load(cmd);
            if (m_HocSinhData.Rows.Count < 1)
                return null; 
            m_HocSinhData.CloseConnection();
            return m_HocSinhData.Rows[0]["HoTen"].ToString();
           
        }
        #region  lay 1 hoc sinh
        public DataRow LayThongTin1HS(String maHS)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("SELECT* FROM Tab_HocSinh where MaHocSinh=@MaHocSinh");
            cmd.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHS;
            m_HocSinhData.Load(cmd);
            if (m_HocSinhData.Rows.Count < 1)
                return null;
            m_HocSinhData.CloseConnection();
            return m_HocSinhData.Rows[0];
        }
        #endregion

        public DataTable LayHocSinh()
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Tab_HocSinh");
            m_HocSinhData.Load(cmd);
            return m_HocSinhData;
        }
        public DataTable LayDSHocSinh()
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Tab_HocSinh HS INNER JOIN Tab_Lop LP ON HS.MaLop = LP.MaLop And HS.RaTruong=0");
            m_HocSinhData.Load(cmd);
            return m_HocSinhData;
        }
        public DataRow ThemDongMoi()
        {
            return m_HocSinhData.NewRow();
        }
        public void ThemHocSinh(DataRow m_Row)
        {
            m_HocSinhData.Rows.Add(m_Row);
        }
        public bool ThemHocSinh()
        {
            return m_HocSinhData.ExecuteNoneQuery() > 0;
        }
        public void ThemHocSinh(HocSinhInfo hs)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO Tab_HocSinh VALUES(@MaHocSinh, @HoTen, @MaLop, @NgaySinh, @NgayVaoDoan, @HoTenCha, @HoTenMe, @SoDTCha, @SoDTMe, @SoDTHS, @TuoiCha, @TuoiMe, @NgheCha, @NgheMe, @Email, @NoiSinh, @GioiTinh, @THCS, @MaChucVu, @DiemBiThu, @NhanXet, @AnhHocSinh, @RaTruong, @DoanVien)");
            cmd.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = hs.MaHocSinh;
            cmd.Parameters.Add("HoTen", SqlDbType.NVarChar).Value = hs.HoTen;
            cmd.Parameters.Add("MaLop", SqlDbType.Char).Value = hs.MaLop;
            cmd.Parameters.Add("NgaySinh", SqlDbType.Char).Value = hs.NgaySinh;
            cmd.Parameters.Add("NgayVaoDoan", SqlDbType.Char).Value = hs.NgayVaoDoan;
            cmd.Parameters.Add("HoTenCha", SqlDbType.NVarChar).Value = hs.HoTenCha;
            cmd.Parameters.Add("HoTenMe", SqlDbType.NVarChar).Value = hs.HoTenMe;
            cmd.Parameters.Add("SoDTCha", SqlDbType.Char).Value = hs.SoDTCha;
            cmd.Parameters.Add("SoDTMe", SqlDbType.Char).Value = hs.SoDTMe;
            cmd.Parameters.Add("SoDTHS", SqlDbType.Char).Value = hs.SoDTHS;
            cmd.Parameters.Add("TuoiCha", SqlDbType.Int).Value = hs.TuoiCha;
            cmd.Parameters.Add("TuoiMe", SqlDbType.Int).Value = hs.TuoiMe;
            cmd.Parameters.Add("NgheCha", SqlDbType.NVarChar).Value = hs.NgheCha;
            cmd.Parameters.Add("NgheMe", SqlDbType.NVarChar).Value = hs.NgheMe;
            cmd.Parameters.Add("Email", SqlDbType.Char).Value = hs.Email;
            cmd.Parameters.Add("NoiSinh", SqlDbType.NVarChar).Value = hs.NoiSinh;
            cmd.Parameters.Add("GioiTinh", SqlDbType.NVarChar).Value = hs.GioiTinh;
            cmd.Parameters.Add("THCS", SqlDbType.NVarChar).Value = hs.THCS;
            cmd.Parameters.Add("MaChucVu", SqlDbType.Char).Value = hs.MaChucVu;
            cmd.Parameters.Add("DiemBiThu", SqlDbType.Float).Value = hs.DiemBiThu;
            cmd.Parameters.Add("NhanXet", SqlDbType.NVarChar).Value = hs.NhanXet;
            cmd.Parameters.Add("AnhHocSinh", SqlDbType.NVarChar).Value = hs.AnhHocSinh;
            cmd.Parameters.Add("RaTruong", SqlDbType.Int).Value = hs.RaTruong;
            cmd.Parameters.Add("DoanVien", SqlDbType.Int).Value = hs.DoanVien;

            m_HocSinhData.Load(cmd);

        }

        public bool ChuyenHocSinh(String malopdi,String malopden)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("UPDATE Tab_HocSinh SET MaLop = @MaLopDen Where MaLop = @MaLopDi");
     
            cmd.Parameters.Add("MaLopDi", SqlDbType.Char).Value =malopdi;
            cmd.Parameters.Add("MaLopDen", SqlDbType.Char).Value = malopden;

            m_HocSinhData.Load(cmd);
            try
            {
                m_HocSinhData.ExecuteNoneQuery(cmd);
                m_HocSinhData.CloseConnection();
                return true;
            }
            catch
            {
                m_HocSinhData.CloseConnection();
                return false;
            }
        }

        public bool DanhDau_RaTruong(String malop,int rt)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("UPDATE Tab_HocSinh SET RaTruong = @RaTruong Where MaLop = @MaLop");

            cmd.Parameters.Add("MaLop", SqlDbType.Char).Value = malop;
            cmd.Parameters.Add("RaTruong", SqlDbType.Int).Value = rt;

            m_HocSinhData.Load(cmd);
            try
            {
                m_HocSinhData.ExecuteNoneQuery(cmd);
                m_HocSinhData.CloseConnection();
                return true;
            }
            catch
            {
                m_HocSinhData.CloseConnection();
                return false;
            }
        }
        public bool SuaHocSinh(HocSinhInfo hs)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("UPDATE Tab_HocSinh SET HoTen = @HoTen, MaLop = @MaLop, NgaySinh = @NgaySinh, NgayVaoDoan = @NgayVaoDoan, HoTenCha = @HoTenCha, HoTenMe = @HoTenMe, SoDTCha = @SoDTCha, SoDTMe = @SoDTMe, SoDTHS = @SoDTHS, TuoiCha = @TuoiCha, TuoiMe = @TuoiMe, NgheCha = @NgheCha, NgheMe = @NgheMe, Email = @Email, NoiSinh = @NoiSinh, GioiTinh = @GioiTinh, THCS = @THCS, MaChucVu = @MaChucVu, DiemBiThu = @DiemBiThu, NhanXet = @NhanXet, AnhHocSinh = @AnhHocSinh, RaTruong = @RaTruong, DoanVien = @DoanVien Where MaHocSinh = @MaHocSinh");
            cmd.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = hs.MaHocSinh;//1
            cmd.Parameters.Add("HoTen", SqlDbType.NVarChar).Value = hs.HoTen;
            cmd.Parameters.Add("NgaySinh", SqlDbType.Char).Value = hs.NgaySinh;
            cmd.Parameters.Add("MaLop", SqlDbType.Char).Value = hs.MaLop;
            cmd.Parameters.Add("NgayVaoDoan", SqlDbType.Char).Value = hs.NgayVaoDoan;
            cmd.Parameters.Add("HoTenCha", SqlDbType.NVarChar).Value = hs.HoTenCha;//6
            cmd.Parameters.Add("HoTenMe", SqlDbType.NVarChar).Value = hs.HoTenMe;
            cmd.Parameters.Add("SoDTCha", SqlDbType.Char).Value = hs.SoDTCha;
            cmd.Parameters.Add("SoDTMe", SqlDbType.Char).Value = hs.SoDTMe;
            cmd.Parameters.Add("SoDTHS", SqlDbType.Char).Value = hs.SoDTHS;
            cmd.Parameters.Add("TuoiCha", SqlDbType.Char).Value = hs.TuoiCha;//11
            cmd.Parameters.Add("TuoiMe", SqlDbType.Char).Value = hs.TuoiMe;
            cmd.Parameters.Add("NgheCha", SqlDbType.NVarChar).Value = hs.NgheCha;
            cmd.Parameters.Add("NgheMe", SqlDbType.NVarChar).Value = hs.NgheMe;
            cmd.Parameters.Add("Email", SqlDbType.Char).Value = hs.Email;
            cmd.Parameters.Add("NoiSinh", SqlDbType.NVarChar).Value = hs.NoiSinh;//16
            cmd.Parameters.Add("GioiTinh", SqlDbType.NVarChar).Value = hs.GioiTinh;
            cmd.Parameters.Add("THCS", SqlDbType.NVarChar).Value = hs.THCS;
            cmd.Parameters.Add("MaChucVu", SqlDbType.Char).Value = hs.MaChucVu;
            cmd.Parameters.Add("DiemBiThu", SqlDbType.Float).Value = hs.DiemBiThu;
            cmd.Parameters.Add("NhanXet", SqlDbType.NVarChar).Value = hs.NhanXet; //21
            cmd.Parameters.Add("AnhHocSinh", SqlDbType.NVarChar).Value = hs.AnhHocSinh;
            cmd.Parameters.Add("RaTruong", SqlDbType.Int).Value = hs.RaTruong;
            cmd.Parameters.Add("DoanVien", SqlDbType.Int).Value = hs.DoanVien; //24

            m_HocSinhData.Load(cmd);
            try
            {
                m_HocSinhData.ExecuteNoneQuery(cmd);
                m_HocSinhData.CloseConnection();
                return true;
            }
            catch
            {
                m_HocSinhData.CloseConnection();
                return false;
            }
        }

        public bool capNhatNhanXet(string maHS, string nhanXet)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("UPDATE Tab_HocSinh SET NhanXet = @NhanXet Where MaHocSinh = @MaHocSinh");
            cmd.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHS;//1
           
            cmd.Parameters.Add("NhanXet", SqlDbType.NVarChar).Value = nhanXet; //21

            try
            {
                m_HocSinhData.ExecuteNoneQuery(cmd);
                m_HocSinhData.CloseConnection();
                return true;
            }
            catch
            {
                m_HocSinhData.CloseConnection();
                return false;
            }

        }
        public bool XoaHocSinh(String mahs)
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM Tab_HocSinh WHERE MaHocSinh = @MaHocSinh");
            cmd.Parameters.Add("MaHocSinh", SqlDbType.VarChar).Value = mahs;
            m_HocSinhData.Load(cmd);
            try
            {
                m_HocSinhData.ExecuteNoneQuery(cmd);
                m_HocSinhData.CloseConnection();
                return true;
            }
            catch
            {
                m_HocSinhData.CloseConnection();
                return false;
            }
        }

        public DataTable layRenLuyenHS(string maHocSinh)
        {
            DataService.OpenConnection();
            string sql = "select distinct hs.MaHocSinh, hs.HoTen,l.TenLop,hd.TenHoatDong,hdhs.MucDo,kl.TenKyLuat,CONVERT(varchar(20), hsvp.NgayViPham, 103) AS NgayViPham,hsvp.GhiChu ";
            sql += "from Tab_HocSinh hs, Tab_HoatDong hd, Tab_HoatDongHocSinh hdhs, Tab_HocSinhViPham hsvp,Tab_KyLuat kl,Tab_Lop l";
            sql += " where hs.MaHocSinh = hdhs.MaHocSinh and hdhs.MaHoatDong=hd.MaHoatDong and hsvp.MaHocSinh=hs.MaHocSinh and hsvp.MaKyLuat=kl.MaKyLuat ";
            sql+=" and l.MaLop = hs.MaLop and  hs.MaHocSinh=@MaHocSinh";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHocSinh", SqlDbType.Char).Value = maHocSinh;
            m_HocSinhData.Load(command);
            m_HocSinhData.CloseConnection();
            return m_HocSinhData;
        }
        public DataTable layRenLuyenHSTheoLop(string maLop)
        {
            DataService.OpenConnection();
            string sql = "select distinct hs.MaHocSinh, hs.HoTen,l.TenLop,hd.TenHoatDong,hdhs.MucDo,kl.TenKyLuat,CONVERT(varchar(20), hsvp.NgayViPham, 103) AS NgayViPham,hsvp.GhiChu ";
            sql += "from Tab_HocSinh hs, Tab_HoatDong hd, Tab_HoatDongHocSinh hdhs, Tab_HocSinhViPham hsvp,Tab_KyLuat kl,Tab_Lop l";
            sql += " where hs.MaHocSinh = hdhs.MaHocSinh and hdhs.MaHoatDong=hd.MaHoatDong and hsvp.MaHocSinh=hs.MaHocSinh and hsvp.MaKyLuat=kl.MaKyLuat ";
            sql += " and l.MaLop = hs.MaLop and  l.MaLop=@MaLop";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaLop", SqlDbType.Char).Value = maLop;
            m_HocSinhData.Load(command);
            m_HocSinhData.CloseConnection();
            return m_HocSinhData;
        }
        #region Tim Kiem
        public DataTable TimKiemTheoMa(String id)
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Tab_HocSinh WHERE MaHocSinh LIKE '%@id%'");
            cmd.Parameters.Add("id", SqlDbType.Char).Value = id;

            m_HocSinhData.Load(cmd);
            return m_HocSinhData;
        }

        public DataTable TimKiemTheoTen(String ten)
        {
            SqlCommand cmd = new SqlCommand("SELECT hs.AnhHocSinh, hs.MaHocSinh,hs.HoTen,hs.MaLop,l.TenLop FROM Tab_HocSinh hs inner join Tab_Lop l on hs.MaLop=l.MaLop WHERE HoTen LIKE N'%"+ten+"%'");
          
            m_HocSinhData.Load(cmd);
            return m_HocSinhData;
        }
        #endregion

    }
}
